﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.IO;
using System.Data.OleDb;
using System.Data;
using Library.Font;
using System.Data.SqlClient;

namespace ImportExamResultApplication
{
    public partial class _Default : System.Web.UI.Page
    {
        const int CountPattern = 200;
        const string VFoxproConnectionString = "Provider=vfpoledb;Data Source={0};Collating Sequence=machine;";
        const string Excel97ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;\"";
        const string Excel07ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES\"";
        private string SqlConnectionString = System.Configuration.ConfigurationManager.
                ConnectionStrings["TuyenSinhConnectionString"].ConnectionString;

        #region Members & Properties

        private DataTable _tblTruongDaiHoc;
        private static IDictionary<string, string> _captiDic = new Dictionary<string, string>();
        private static IDictionary<string, string> _fieldDic = new Dictionary<string, string>();

        #endregion

        public _Default()
        {
            _tblTruongDaiHoc = LoadUniversities();
            if (_fieldDic.Count == 0)
            {
                _fieldDic.Add(Fields.Std.SBD, Fields.Dbf.SBD);
                _fieldDic.Add(Fields.Std.HoTen, Fields.Dbf.HoTen);
                _fieldDic.Add(Fields.Std.NgaySinh, Fields.Dbf.NgaySinh);
                _fieldDic.Add(Fields.Std.MaNganh, Fields.Dbf.MaNganh);
                _fieldDic.Add(Fields.Std.MaTruong, Fields.Dbf.MaTruong);
                _fieldDic.Add(Fields.Std.KhoiThi, Fields.Dbf.KhoiThi);
                _fieldDic.Add(Fields.Std.DM1, Fields.Dbf.DM1);
                _fieldDic.Add(Fields.Std.DM2, Fields.Dbf.DM2);
                _fieldDic.Add(Fields.Std.DM3, Fields.Dbf.DM3);
                _fieldDic.Add(Fields.Std.DM4, Fields.Dbf.DM4);
                _fieldDic.Add(Fields.Std.DM5, Fields.Dbf.DM5);
                _fieldDic.Add(Fields.Std.DM6, Fields.Dbf.DM6);
                _fieldDic.Add(Fields.Std.TongDiem, Fields.Dbf.Dtc);

                _captiDic.Add(Fields.Std.SBD, "Số báo danh");
                _captiDic.Add(Fields.Std.HoTen, "Họ tên");
                _captiDic.Add(Fields.Std.NgaySinh, "Ngày sinh");
                _captiDic.Add(Fields.Std.MaNganh, "Mã ngành");
                _captiDic.Add(Fields.Std.MaTruong, "Mã trường");
                _captiDic.Add(Fields.Std.KhoiThi, "Khối thi");
                _captiDic.Add(Fields.Std.DM1, "Môn 1");
                _captiDic.Add(Fields.Std.DM2, "Môn 2");
                _captiDic.Add(Fields.Std.DM3, "Môn 3");
                _captiDic.Add(Fields.Std.DM4, "Môn 4");
                _captiDic.Add(Fields.Std.DM5, "Môn 5");
                _captiDic.Add(Fields.Std.DM6, "Môn 6");
                _captiDic.Add(Fields.Std.TongDiem, "Tổng cộng");
            }
        }

        #region Events

        protected void Page_Load(object sender, EventArgs e)
        {
            this.litErrorMessage.Text = string.Empty;
            this.btnSend.Click += new EventHandler(btnSend_Click);
            this.btnStandard.Click += new EventHandler(btnStandard_Click);
            this.btnExport.Click += new EventHandler(btnExport_Click);

            if (!IsPostBack)
            {

            }

            if (fileUpload.PostedFile != null)
            {
                // File was sent
            }
            else
            {
                // No file
            }
        }

        private System.Web.UI.HtmlControls.HtmlForm GetDataToExport()
        {
            var gridView = new GridView();
            gridView.DataSource = SessionUtils.Get<DataTable>("ResultTable");
            gridView.DataBind();

            var form = new System.Web.UI.HtmlControls.HtmlForm();
            form.Controls.Add(gridView);

            return form;
        }

        void btnExport_Click(object sender, EventArgs e)
        {
            string attachment = String.Format("attachment; filename=diemthi{0:yyyyMMdd}.xls", DateTime.Now);
            Response.ClearContent();
            Response.AddHeader("content-disposition", attachment);
            Response.ContentType = "application/ms-excel";
            Response.Charset = String.Empty;
            Response.ContentEncoding = System.Text.Encoding.Unicode;
            Response.BinaryWrite(System.Text.Encoding.Unicode.GetPreamble());

            System.IO.StringWriter stw = new System.IO.StringWriter();
            HtmlTextWriter htextw = new HtmlTextWriter(stw);
            var form = GetDataToExport();
            this.Controls.Add(form);
            form.RenderControl(htextw);

            Response.Write(stw.ToString());
            Response.End();
        }

        protected void btnStandard_Click(object sender, EventArgs e)
        {
            try
            {
                // Standard
                ConvertFont convertFont = new ConvertFont(ConvertFont.VietEncodings.TCVN3);
                DataTable sourceTable = SessionUtils.Get<DataTable>("ResultTable");
                int divide = 1, i = 0, count = Math.Min(CountPattern, sourceTable.Rows.Count);
                while (i < count)
                {
                    float? d = ConvertMark(sourceTable.Rows[i++][_fieldDic[Fields.Std.DM1]], divide);
                    if (d > 100)
                    {
                        divide = 100;
                        break;
                    }
                }

                DataTable table = GetStandardList(convertFont, sourceTable, divide);

                if (this.chkMergeSheet.Checked && ddlSheetName.Items.Count > 1)
                {
                    FileInfo fileInfo = new FileInfo(SessionUtils.Get<string>("Path"));
                    string connectionString = fileInfo.Extension.ToLower() == ".xls" ? Excel97ConnectionString : Excel07ConnectionString;
                    for (i = 1; i < ddlSheetName.Items.Count; i++)
                    {
                        DataTable mergeTable = LoadFileXls(fileInfo, connectionString, (ddlSheetName.Items[i] as ListItem).Value);
                        table.Merge(GetStandardList(convertFont, mergeTable, divide));
                    }
                }

                this.litUniversityId.Text = table.Rows[0][Fields.Std.MaTruong].ToString();
                this.txtUniversityName.Text = GetUniversityName(this.litUniversityId.Text);
                this.pnlUniversity.Visible = true;

                SessionUtils.Set("ResultTable", table);
                Grid.DataSource = table;
                Grid.DataBind();

                btnStandard.Enabled = false;
                btnExport.Enabled = true;
                btnExecute.Enabled = true;
                //btnMapFields.Enabled = false;
            }
            catch (Exception ex)
            {
                litErrorMessage.Text = "Chuẩn hóa file gặp lỗi (Có thể bạn chưa thực hiện map field chuẩn hóa đúng)." + Environment.NewLine
                                    + "Vui lòng kiểm tra lại! Chi tiết: " + ex.Message;
            }
        }

        private DataTable GetStandardList(ConvertFont convertFont, DataTable sourceTable, int divide)
        {
            DataTable table = new DataTable("ThiSinh_DaiHoc");
            table.Columns.Add(Fields.Std.SBD);
            table.Columns.Add(Fields.Std.HoTen);
            table.Columns.Add(Fields.Std.NgaySinh);
            table.Columns.Add(Fields.Std.MaNganh);
            table.Columns.Add(Fields.Std.MaTruong);
            table.Columns.Add(Fields.Std.KhoiThi);
            table.Columns.Add(Fields.Std.DM1);
            table.Columns.Add(Fields.Std.DM2);
            table.Columns.Add(Fields.Std.DM3);
            table.Columns.Add(Fields.Std.DM4);
            table.Columns.Add(Fields.Std.DM5);
            table.Columns.Add(Fields.Std.DM6);
            table.Columns.Add(Fields.Std.TongDiem);

            foreach (DataRow item in sourceTable.Rows)
            {
                if (item[_fieldDic[Fields.Std.SBD]] == DBNull.Value || item[_fieldDic[Fields.Std.SBD]].ToString().Trim().Length == 0) continue;

                DataRow row = table.NewRow();
                row[Fields.Std.SBD] = item[_fieldDic[Fields.Std.SBD]];
                row[Fields.Std.MaTruong] = this.chkAddUniversityId.Checked ? this.txtUniversityId.Text : item[_fieldDic[Fields.Std.MaTruong]].ToString().Trim();

                // Aditional

                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.HoTen]))
                {
                    row[Fields.Std.HoTen] = convertFont.Convert(item[_fieldDic[Fields.Std.HoTen]].ToString()).Trim();
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.KhoiThi]))
                {
                    row[Fields.Std.KhoiThi] = item[_fieldDic[Fields.Std.KhoiThi]];
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.NgaySinh]))
                {
                    row[Fields.Std.NgaySinh] = ConvertBirthDay(item[_fieldDic[Fields.Std.NgaySinh]]);
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.MaNganh]))
                {
                    row[Fields.Std.MaNganh] = item[_fieldDic[Fields.Std.MaNganh]];
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.DM1]))
                {
                    row[Fields.Std.DM1] = ConvertMark(item[_fieldDic[Fields.Std.DM1]], divide);
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.DM2]))
                {
                    row[Fields.Std.DM2] = ConvertMark(item[_fieldDic[Fields.Std.DM2]], divide);
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.DM3]))
                {
                    row[Fields.Std.DM3] = ConvertMark(item[_fieldDic[Fields.Std.DM3]], divide);
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.DM4]))
                {
                    row[Fields.Std.DM4] = ConvertMark(item[_fieldDic[Fields.Std.DM4]], divide);
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.DM5]))
                {
                    row[Fields.Std.DM5] = ConvertMark(item[_fieldDic[Fields.Std.DM5]], divide);
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.DM6]))
                {
                    row[Fields.Std.DM6] = ConvertMark(item[_fieldDic[Fields.Std.DM6]], divide);
                }
                if (sourceTable.Columns.Contains(_fieldDic[Fields.Std.TongDiem]))
                {
                    row[Fields.Std.TongDiem] = ConvertMark(item[_fieldDic[Fields.Std.TongDiem]], divide);
                }

                table.Rows.Add(row);
            }

            return table;
        }

        protected void btnSend_Click(object sender, EventArgs e)
        {
            HttpPostedFile myfileUpload = fileUpload.PostedFile;
            if (myfileUpload != null)
            {
                var fileName = "D:\\" + Path.GetFileName(myfileUpload.FileName);
                SessionUtils.Set("Path", fileName);
                myfileUpload.SaveAs(fileName);
                FileInfo fileInfo = new FileInfo(fileName);
                var result = LoadFile(fileInfo);

                btnExport.Enabled = false;
                btnExecute.Enabled = false;
                btnStandard.Enabled = result;
            }
        }

        #endregion

        private DataTable LoadFileDbf(FileInfo fileInfo)
        {
            DataTable table = new DataTable();

            using (OleDbConnection connection = new OleDbConnection(string.Format(VFoxproConnectionString, fileInfo.Directory.FullName)))
            {
                try
                {
                    var sql = "SELECT * FROM " + fileInfo.Name;
                    using (OleDbCommand command = new OleDbCommand(sql, connection))
                    {
                        connection.Open();
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception ex)
                {
                    litErrorMessage.Text = ex.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            return table;
        }

        private DataTable LoadFileXls(FileInfo fileInfo, string connectionString, string sheetName)
        {
            DataTable table = new DataTable();
            using (OleDbConnection connection = new OleDbConnection(string.Format(connectionString, fileInfo.FullName)))
            {
                try
                {
                    connection.Open();
                    if (string.IsNullOrEmpty(sheetName) || sheetName.Trim().Length == 0)
                    {
                        ddlSheetName.DataSource = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        ddlSheetName.DataValueField = "TABLE_NAME";
                        ddlSheetName.DataTextField = "TABLE_NAME";
                        ddlSheetName.DataBind();

                        if (ddlSheetName.Items.Count > 0)
                        {
                            sheetName = (ddlSheetName.DataSource as DataTable).Rows[0]["TABLE_NAME"].ToString();
                        }
                        else
                        {
                            return table;
                        }
                    }

                    var sql = "Select * from [" + (sheetName.EndsWith("$") ? sheetName : (sheetName + '$')) + "]";
                    using (OleDbCommand command = new OleDbCommand(sql, connection))
                    {
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception ex)
                {
                    litErrorMessage.Text = "Tải file excel gặp lỗi." + Environment.NewLine
                        + "Bạn hãy đặt lại tên sheet đơn giản và thử lại lần nữa (ví dụ: Sheet1)." + Environment.NewLine
                        + "Chi tiết: " + ex.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            return table;
        }

        private bool LoadFile(FileInfo fileInfo)
        {
            bool result = false;
            DataTable table;
            switch (fileInfo.Extension.ToLower())
            {
                case ".dbf":
                    table = LoadFileDbf(fileInfo);
                    this.pnlSheet.Visible = false;

                    _fieldDic[Fields.Std.MaTruong] = Fields.Dbf.MaTruong;
                    _fieldDic[Fields.Std.KhoiThi] = Fields.Dbf.KhoiThi;
                    _fieldDic[Fields.Std.MaNganh] = Fields.Dbf.MaNganh;
                    _fieldDic[Fields.Std.HoTen] = Fields.Dbf.HoTen;
                    _fieldDic[Fields.Std.NgaySinh] = Fields.Dbf.NgaySinh;
                    _fieldDic[Fields.Std.SBD] = Fields.Dbf.SBD;
                    _fieldDic[Fields.Std.DM1] = Fields.Dbf.DM1;
                    _fieldDic[Fields.Std.DM2] = Fields.Dbf.DM2;
                    _fieldDic[Fields.Std.DM3] = Fields.Dbf.DM3;
                    _fieldDic[Fields.Std.DM4] = Fields.Dbf.DM4;
                    _fieldDic[Fields.Std.DM5] = Fields.Dbf.DM5;
                    _fieldDic[Fields.Std.DM6] = Fields.Dbf.DM6;
                    _fieldDic[Fields.Std.TongDiem] = Fields.Dbf.Dtc;
                    break;
                case ".xls":
                    table = LoadFileXls(fileInfo, Excel97ConnectionString, ddlSheetName.Text);
                    this.pnlSheet.Visible = true;
                    break;
                case ".xlsx":
                    table = LoadFileXls(fileInfo, Excel07ConnectionString, ddlSheetName.Text);
                    this.pnlSheet.Visible = true;
                    break;
                default:
                    table = new DataTable();
                    break;
            }

            SessionUtils.Set("ResultTable", table);
            Grid.DataSource = table;
            Grid.DataBind();

            result = true;

            return result;
        }

        private float? ConvertMark(object item, int divide)
        {
            float? diem = null;
            if (item != DBNull.Value && item.ToString().Trim().Length > 0)
            {
                string value = item.ToString().Trim();
                float f;
                if (float.TryParse(value, out f))
                {
                    diem = Convert.ToSingle(item);
                    diem /= divide;
                }
                else if (value == ".")
                {
                    diem = 0;
                }
            }
            return diem;
        }

        [Serializable]
        public class Template
        {
            public string key { get; set; }
            public string value { get; set; }
            public string caption { get; set; }

            public Template(string key, string value, string caption = null)
            {
                this.key = key;
                this.value = value;
                this.caption = caption;
            }
        }

        [System.Web.Services.WebMethod]
        public static object GetTableColumns()
        {
            DataTable table = SessionUtils.Get<DataTable>("ResultTable");
            IList<string> temp = new List<string>();
            IDictionary<string, Template> columns = new Dictionary<string, Template>();
            foreach (DataColumn item in table.Columns)
            {
                if (!_fieldDic.Values.Contains(item.ColumnName.ToLower()))
                {
                    if (columns.ContainsKey(item.ColumnName)) continue;
                    columns.Add(item.ColumnName, new Template(item.ColumnName, item.ColumnName));
                }
                else
                {
                    temp.Add(item.ColumnName.ToLower());
                }
            }
            var columns1 = new List<Template>();
            foreach (var item in _fieldDic)
            {
                if (temp.Contains(item.Value))
                    columns1.Add(new Template(item.Key, item.Value, _captiDic[item.Key]));
                else
                    columns1.Add(new Template(item.Key, string.Empty, _captiDic[item.Key]));
            }
            return new { Items = columns.Values.ToList(), SelectedItems = columns1 };
        }

        [System.Web.Services.WebMethod]
        public static void SaveColumns(object[] list)
        {
            foreach (var item in list as Template[])
            {
                //_fieldDic[item.key] = item.value;
            }
        }

        private string ConvertBirthDay(object item)
        {
            if (item == DBNull.Value || item.Equals(string.Empty))
                return null;

            string date = item.ToString().Trim();

            if (rdoFormatDate.SelectedIndex == 1)
            {
                string dd = date.Substring(0, 2);
                date = date.Remove(0, 2).Insert(2, dd);
            }

            if (date.Length == 6 || date.Length == 8)
            {
                return date.Insert(4, "/").Insert(2, "/");
            }
            else if (date.Length == 4)
            {
                return date.Insert(2, "/");
            }
            else
            {
                return date;
            }
        }

        private string GetUniversityName(string universityId)
        {
            DataRow[] rows = _tblTruongDaiHoc.Select("ID = '" + universityId + "'");
            if (rows.Length > 0)
            {
                return rows[0]["Title"].ToString();
            }
            return string.Empty;
        }


        private DataTable LoadUniversities()
        {
            DataTable table = new DataTable();

            using (SqlConnection connection = new SqlConnection(SqlConnectionString))
            {
                try
                {
                    var sql = "SELECT RTRIM(ID) AS ID, Title FROM TruongDaiHoc_Chuan";
                    using (SqlCommand command = new SqlCommand(sql, connection))
                    {
                        connection.Open();
                        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                        {
                            adapter.Fill(table);
                        }
                    }
                }
                catch (Exception ex)
                {
                    litErrorMessage.Text = "Load danh sách trường chuẩn bị lỗi! Chi tiết: " + ex.Message;
                }
                finally
                {
                    if (connection.State == ConnectionState.Open)
                    {
                        connection.Close();
                    }
                }
            }

            return table;
        }

        
    }
}
